package com.weavernorth.retailExperienceAward;

import com.weavernorth.retailExperienceAward.utils.commonsUtil;
import com.weavernorth.sysap.util.SyncUtil;
import com.weavernorth.util.Logger4jUtil;
import com.weavernorth.util.dateUtils;
import jxl.read.biff.Record;
import org.apache.log4j.Logger;
import weaver.conn.ConnectionPool;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetTrans;
import weaver.general.BaseBean;
import weaver.general.Util;
import weaver.interfaces.schedule.BaseCronJob;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Statement;

import static com.weavernorth.retailExperienceAward.utils.commonsUtil.doubleFormat;
import static com.weavernorth.util.dateUtils.getHourMs;

/**
 * @Author: bruseewang
 * @Package: com.weavernorth.qyjlAvgGradeOfBCDTimmer
 * @date: 2020/10/20 10:40
 * @description: 定时任务：非A/A+店铺对应的区域经理平均分计算
 */
public class qyjlAvgGradeOfBCDTimmer extends BaseCronJob {
    public static ConnectionPool pool = ConnectionPool.getInstance();
    public Connection conn;
    public Statement statement;
    public String cycle;

    public String getcycle() {
        return cycle;
    }

    public void setcycle(String cycle) {
        this.cycle = cycle;
    }

    @Override
    public void execute() {
        Logger log = Logger4jUtil.getLoggerByName("/Timmer/qyjlAvgGradeOfBCDTimmer/");
        log.info("区域经理(BCD店铺所属)分数计算接口执行开始----：");
        RecordSet rs = new RecordSet();
        String strKhzq = dateUtils.previousMonth(0);
        try {
            if (cycle != null) {
                strKhzq = dateUtils.previousMonth(Integer.parseInt(cycle));
            }
        } catch (Exception ex) {
            log.info("异常信息：" + ex.toString());
        }
        log.info("考核周期为：" + strKhzq);
        String strkhrq = dateUtils.getPreviousMonthDate();//考核日期(由原先取当前日期改为取上个月日期）
        log.info("考核日期为：" + strkhrq);
        SyncUtil syncUtil = new SyncUtil();
        String strFormid = syncUtil.getModeIdBytableName("uf_zddpgrjj_qyjl");
        String nowDate = dateUtils.getYearMonthDay();
        log.info("当前日期：" + nowDate);
        String nowTime = getHourMs();
        log.info("当前时间：" + nowTime);
        try {
            //获取所有以分公司,区域经理为维度的非A/A+店铺对应的平均分
            String strSqls = "SELECT\n" +
                    "\ta.fgs,h.JOBTITLE,\n" +
                    "\t\tTO_CHAR(a.qyjl) qyjl,h.workcode gh,\n" +
                    "\t\tROUND( AVG( b.fwbz40 ), 2 ) AS fwbz_avg,\n" +
                    "\t\tROUND( AVG( b.clbz20 ), 2 ) AS clbz_avg,\n" +
                    "\t\tROUND( AVG( b.crm5 ), 2 ) AS crm_avg\n" +
                    "\t\tROUND( AVG( b.hr10 ), 2 ) AS hr_avg\n" +
                    "\tFROM\n" +
                    "\t\tuf_dpxx a,\n" +
                    "\t\tuf_zddpfstj b,\n" +
                    "\t\tuf_cfdplstykh c,hrmresource h \n" +
                    "\tWHERE\n" +
                    "\t\ta.fgs=b.fgs\n" +
                    "\t\tand a.fgs=c.fgsppdy\n" +
                    "\t\tAND a.dp=b.dp AND TO_CHAR(a.QYJL)=h.ID\n" +
                    "\t\tAND a.fgs IS NOT NULL \n" +
                    "\t\tAND a.qyjl IS NOT NULL \n" +
                    "\t\tAND a.dp is not null\n" +
                    "\t\tand a.dpjb is not null\n" +
                    "\t\tAND a.dpzt=0\n" +
                    "\t\tand a.dpjb NOT IN(0,1)\n" +
                    "  \tAND b.khzq='" + strKhzq + "'\n" +
                    " AND NOT EXISTS (SELECT DISTINCT to_char(f.QYJL) FROM uf_dpxx f WHERE f.DPJB in(0,1) AND to_char(f.QYJL)=to_char(a.QYJL)) " +
                    "\t\tAND NOT EXISTS(SELECT 1 FROM uf_yddpzxygxxd d WHERE  d.khzq=b.khzq AND d.fgs=a.fgs and d.dp=b.dp)\n" +
                    " GROUP BY a.fgs,TO_CHAR(a.qyjl),h.workcode,h.JOBTITLE\n" +
                    "\t\t\n";
            log.info("获取所有非A/A+店铺对应的区域经理所属店铺的平均分sql:" + strSqls);
            rs.execute(strSqls);
            conn = pool.getConnection();
            statement = conn.createStatement();
            String strOperationSql = "";
            while (rs.next()) {
                //区域经理
                String strXm = Util.null2String(rs.getString("qyjl"));
                //分公司
                String strFgs = Util.null2String(rs.getString("fgs"));
                //工号
                String strGh = Util.null2String(rs.getString("gh"));
                //岗位
                String strGw = Util.null2String(rs.getString("JOBTITLE"));
                //服务标准
                BigDecimal fwbz = new BigDecimal(Util.null2o(rs.getString("fwbz_avg")));
                //陈列标准
                BigDecimal clbz = new BigDecimal(Util.null2o(rs.getString("clbz_avg")));
                //CRM
                BigDecimal crm = new BigDecimal(Util.null2o(rs.getString("crm_avg")));
                //hr
                BigDecimal hr = new BigDecimal(Util.null2o(rs.getString("hr_avg")));
                Double qjts = getQjDays(strKhzq, strXm);
                log.info("分公司：" + strFgs + ";区域经理：" + strXm + ";服务标准：" + fwbz + ";陈列标准：" + clbz + ";hr评分：" + hr + ";CRM：" + crm + ";总得分：;请假天数：" + qjts + ";奖金金额：;工号：" + strGh);

                //判断是否更新数据
                if (isUpdate(strXm, strFgs, strKhzq)) {
                    log.info("数据已存在,进行更新操作！");
                    strOperationSql = "update uf_zddpgrjj_qyjl set zw='" + strGw + "',zdf=null,yybz35=null,clbz20='" + clbz + "',fwbz40='" + fwbz + "',hr10='" + hr + "',crm5='" + crm + "',qjts='" + qjts + "',jjje=null,gh='" + strGh + "',MODEDATAMODIFIER='2',MODEDATAMODIFYDATETIME='"+nowDate+" "+getHourMs()+"' where fgs='" + strFgs + "' and xm='" + strXm + "' and khzq='" + strKhzq + "'";
                    log.info("更新sql:" + strOperationSql);
                    statement.addBatch(strOperationSql);
                } else {
                    log.info("新增操作！");
                    strOperationSql = "INSERT INTO uf_zddpgrjj_qyjl (zw,fgs,xm,gh,khzq,khrq,clbz20,crm5,fwbz40,hr10,qjts,formmodeid,modedatacreater,modedatacreatertype,modedatacreatedate,modedatacreatetime)"
                            + " VALUES('" + strGw.trim() + "','" + strFgs + "','" + strXm + "','" + strGh + "','" + strKhzq + "','" + strkhrq + "','" + clbz + "','" + crm + "','" + fwbz + "','" + hr + "','" + qjts + "','" + strFormid + "',1,0,'" + nowDate + "','" + nowTime + "')";
                    log.info("新增sql:" + strOperationSql);
                    statement.addBatch(strOperationSql);
                }
//                String strGetMessage = operation(strOperationSql);
//                log.info(strGetMessage);
            }
            Long begin1 = System.currentTimeMillis();
            statement.executeBatch();
            Long end1 = System.currentTimeMillis();
            log.info("批量更新耗时：" + (end1 - begin1) + "(毫秒)");
            conn.close();
            statement.close();
            log.info("区域经理(BCD店铺所属)分数计算接口执行结束----：");
        } catch (Exception ex) {
            log.info("区域经理(BCD店铺所属)分数计算接口异常：" + ex.toString());
        }
    }

    /***
     * 判断是否更新数据
     * @param strXm
     * @param strFgs
     * @param strKhzq
     */
    private Boolean isUpdate(String strXm, String strFgs, String strKhzq) {
        RecordSet rsQuery = new RecordSet();
        Boolean result = false;
        String strQuery = "SELECT * FROM uf_zddpgrjj_qyjl WHERE xm='" + strXm + "' AND khzq='" + strKhzq + "'";
        rsQuery.execute(strQuery);
        while (rsQuery.next()) {
            result = true;
            break;
        }
        return result;
    }

    /***
     * 进行更新或新增操作
     * @param strSql
     * @return
     */
    private String operation(String strSql) {
        RecordSetTrans rsTrans = new RecordSetTrans();
        String strResult = "0|操作成功！";
        try {
            rsTrans.setAutoCommit(false);
            rsTrans.executeQuery(strSql);
            rsTrans.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
            rsTrans.rollback();
            strResult = "1|" + ex.toString();
        } finally {
            return strResult;
        }
    }

    /***
     * 得到区域经理负责店铺的营运标准评分的平均分
     * @param strKhzq 考核周期
     * @param strxm 区域经理
     * @return
     */
    private String getAvg_yybz(Logger log, String strKhzq, String strxm) {
        RecordSet rs_getZfs_avg = new RecordSet();//获取周期内店铺总分数的平均分
        String strGetAvg = "";//平均分
        String strYybzfs = "";//营运标准评分
        Integer count_All = 0;//区域经理负责的所有店铺数量
        Integer counts = 0;//区域经理负责的已审批完的店铺数量
        String strGetAll = "SELECT COUNT(a.dp) allCount FROM  uf_dpxx a,uf_cfdplstykh b \n" +
                "WHERE a.FGS = b.fgsppdy  AND a.dpzt = 0 and NOT EXISTS(SELECT 1 FROM uf_yddpzxygxxd h WHERE a.FGS=h.fgs AND a.DP=h.dp AND h.khzq='" + strKhzq + "') and TO_CHAR(a.qyjl)='" + strxm + "' GROUP BY TO_CHAR(a.qyjl)";
        log.info("获取区域经理负责下所有的店铺：" + strGetAll);
        RecordSet rs_All = new RecordSet();
        rs_All.executeQuery(strGetAll);
        while (rs_All.next()) {
            count_All = Util.getIntValue(rs_All.getString("allCount"), 0);
        }
        log.info("区域经理" + strxm + ";负责的店铺数量：" + count_All);
        String str_AvgCount = "SELECT a.khzq,a.jcr,count(a.dp) counts FROM uf_qyjlpf a where a.khzq='" + strKhzq + "' and NOT EXISTS(SELECT 1 FROM uf_yddpzxygxxd h WHERE  a.DP=h.dp AND h.khzq='" + strKhzq + "')  and a.jcr='" + strxm + "' GROUP BY a.khzq,a.jcr ";
        log.info("获取区域经理管辖下已审批完的店铺数量Sql:" + str_AvgCount);
        RecordSet rs_counts = new RecordSet();
        rs_counts.executeQuery(str_AvgCount);
        while (rs_counts.next()) {
            counts = Util.getIntValue(rs_counts.getString("counts"), 0);
        }
        log.info("区域经理管辖下已审批的店铺数量：" + counts);
        log.info("区域经理：" + strxm + "所负责店铺总数：" + count_All + ";已审批玩的店铺数：" + counts);
        if (count_All != counts) {
            strYybzfs = "";
        } else {
            String str_getAvg = "SELECT khzq,jcr,sum(zfs) sums FROM uf_qyjlpf where khzq='" + strKhzq + "' and jcr='" + strxm + "' GROUP BY khzq,jcr";
            log.info("获取营运标准平均分sql:" + str_getAvg);

            rs_getZfs_avg.executeQuery(str_getAvg);
            while (rs_getZfs_avg.next()) {
                strGetAvg = Util.null2String(Math.round(Util.getDoubleValue(rs_getZfs_avg.getString("sums"), 0.00) / counts));
                log.info("营运标准平均分：" + strGetAvg);
            }
            if (!"".equals(strGetAvg)) {
                Double avg = Util.getDoubleValue(strGetAvg, 0.00);
                if (avg >= 80) {
                    strYybzfs = "35.00";
                } else {
                    strYybzfs = "0.00";
                }
            }
        }
        return strYybzfs;
    }

    /***
     * 获取请假天数
     * @param strKhzq
     * @param strUserid
     * @return
     */
    private Double getQjDays(String strKhzq, String strUserid) {
        RecordSet rs = new RecordSet();
        Double db_qjDays = 0.00;
        String strSql = "SELECT qjts FROM w_all_leavelist WHERE year=SUBSTR('" + strKhzq + "',1,4) and month==SUBSTR('" + strKhzq + "',5,2) and resourceid='" + strUserid + "'";
        rs.execute(strSql);
        while (rs.next()) {
            db_qjDays = Util.getDoubleValue(rs.getString("qjts"), 0.00);
        }
        return db_qjDays;
    }

    /***
     * 计算奖金
     * @param zdf
     * @param qjts
     * @param strUserid
     * @return
     */
    private Double getAmount(Logger log, BigDecimal zdf, Double qjts, String strUserid, String strFgs) {
        RecordSet rs_gr = new RecordSet();
        RecordSet rs_fgs = new RecordSet();
        Double db_jjjs = 0.00;
        Double db_jjje = 0.00;
        String str_jjjs_gr = null;//个人奖金基数
        //获取个人奖金基数
        String strSql_gr = "SELECT jjjs FROM uf_jjjspzb_gr WHERE  ygxm='" + strUserid + "'";
        log.info("个人奖金基数获取Sql:" + strSql_gr);
        rs_gr.execute(strSql_gr);
        while (rs_gr.next()) {
            str_jjjs_gr = rs_gr.getString("jjjs");
        }
        if (!"".equals(str_jjjs_gr) && str_jjjs_gr != null) {
            db_jjjs = Util.getDoubleValue(str_jjjs_gr, 0.00);
            log.info("个人奖金基数为：" + db_jjjs);
        } else {
            //个人奖金未维护时获取分公司奖金基数
            String strSql_fgs = "SELECT qyjljs FROM uf_jjjspzb WHERE  fgs='" + strFgs + "'";
            log.info("个人奖金基数获取Sql:" + strSql_fgs);
            rs_fgs.execute(strSql_fgs);
            while (rs_fgs.next()) {
                db_jjjs = Util.getDoubleValue(rs_fgs.getString("qyjljs"), 0.00);
                log.info("分公司奖金基数为：" + db_jjjs);
            }
        }
        Double amount = (zdf.doubleValue() / 100) * db_jjjs;
        //根据请假天数调整奖金金额
        db_jjje = doubleFormat(commonsUtil.convertAmount(amount, qjts));
        return db_jjje;
    }
}
